package kin.system.util;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.inject.Inject;

import kin.annotation.system.ExcelField;
import kin.bean.security.Role;
import kin.bean.system.Form;
import kin.service.system.FormServiceManager;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.CellStyle;

import org.springframework.expression.EvaluationContext;
import org.springframework.expression.Expression;
import org.springframework.expression.ExpressionParser;
import org.springframework.expression.spel.standard.SpelExpressionParser;
import org.springframework.expression.spel.support.StandardEvaluationContext;


public class ExcelUtil {
	@Inject
	private FormServiceManager  formServiceManager;
	public FormServiceManager getFormServiceManager() {
		return formServiceManager;
	}


	public void setFormServiceManager(FormServiceManager formServiceManager) {
		this.formServiceManager = formServiceManager;
	}


	public static  void xlsDto2Excel(List xls,Class cls,Form form,ResultSet rs,String exportXlsName) throws Exception {  
        // 获取总列数  
        int CountColumnNum = 0 ; 
        List<String> names = new ArrayList<String>();
        List<String> titleNames= new ArrayList<String>();
       // Map<String,String> names=new HashMap<String,String>();
        HSSFCell[] firstcell = null;
        HSSFCell[] secondcell = null;
        
        //List<HSSFCell> firstcell=new ArrayList<HSSFCell>();
        // 创建Excel文档  
        HSSFWorkbook hwb = new HSSFWorkbook();
        //title字体
        HSSFFont titlefont = hwb.createFont();
        titlefont.setFontName("仿宋_GB2312");
        titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        titlefont.setFontHeightInPoints((short) 12);
         
       // setBorder.setFont(font)
       /* if(rs!=null){
        	ResultSetMetaData rsmd= rs.getMetaData();
        	for(int i=1;i<rsmd.getColumnCount();i++){
        		
        	names[i]=rsmd.getColumnName(i);
        	
        	}
        	
        	
        }else{*/
       // ---------------
      
        	CountColumnNum = xls.size();
            Field[] fields=cls.getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
				
				if (field.isAnnotationPresent(ExcelField.class)){
					ExcelField ef=field.getAnnotation(ExcelField.class);
					names.add(field.getName());
				titleNames.add(ef.columnName());
				}
        	}
        	
        //}
        	firstcell = new HSSFCell[1];
        	secondcell = new HSSFCell[names.size()+1];
        // sheet 对应一个工作页  
	        HSSFSheet sheet = hwb.createSheet("pldrxkxxmb");  
	        HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始  
	        HSSFRow secondrow = sheet.createRow(1);
	        HSSFCellStyle style;
	       //---
	        style=hwb.createCellStyle();
	        firstcell[0]= firstrow.createCell(1);
	        sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 1, (short) titleNames.size()));
	        firstcell[0].setCellValue(new HSSFRichTextString("一、订单基本信息"));
	        style.setAlignment(CellStyle.ALIGN_CENTER);
	        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	      
	        style.setFont(titlefont);
	        sheet.autoSizeColumn(1,false);
	        style.setWrapText(false);
	        firstcell[0].setCellStyle(style);
	   
	        //---
	        style=hwb.createCellStyle();
        for (int j = 0; j < titleNames.size(); j++) {  
        	secondcell[j] =secondrow.createCell(j+1);
        	 
            //合并单元格，参数依次为起始行，结束行，起始列，结束列  
           
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            secondcell[j].setCellValue(new HSSFRichTextString(titleNames.get(j)));  
            secondcell[j].setCellStyle(style ); 
            //sheet.autoSizeColumn(j+1);
        } 
        
        
        for (int i = 0; i < CountColumnNum; i++) {  
            // 创建一行  
            HSSFRow row = sheet.createRow(i + 2);  
            // 得到要插入的每一条记录  
            Object o=xls.get(i); 
           
            for (int colu = 0; colu < names.size(); colu++) {  
                // 在一行内循环  
            	if(names.get(colu)!=null){
            	HSSFCell oo = row.createCell(colu+1);
                   System.out.println(sheet.getColumnWidth(colu+1));
            	 sheet.autoSizeColumn(colu+1);
            	ExcelUtil.getExcelType(o,names.get(colu),oo, hwb, null);
            	}
            }  
        }  
        
        //sheet.autoSizeColumn(1,false);
        // 创建文件输出流，准备输出电子表格  
        OutputStream out = new FileOutputStream("D:/pldrxkxxmb.xls");  
        hwb.write(out);  
        out.close();  
        System.out.println("数据库导出成功");  
    }  
	

	private  static void getExcelType(Object obj, String fieldName,HSSFCell cell,HSSFWorkbook wb,String format) {
		
		EvaluationContext context = new StandardEvaluationContext(obj);
		ExpressionParser parser = new SpelExpressionParser();
		Expression exp = parser.parseExpression(fieldName);
		Object value=exp.getValue(context);
		Class clazz= exp.getValue(context).getClass();
		if(clazz.equals(String.class)||clazz.equals(Character.class)||clazz.equals(char.class)){
			if(""==exp.getValue(context).toString()&&null==exp.getValue(context)){
				cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
				cell.setCellValue("");
			}else{
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(value.toString());
			}
		}else if(clazz.equals(Integer.class) || clazz.equals(Long.class)||clazz.equals(Float.class)){
			cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellValue(Double.parseDouble(value.toString()));
		}else if( clazz.equals(Boolean.class)){
			cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
			cell.setCellValue(Boolean.valueOf(value.toString()));
		}else if(clazz.equals(Date.class) ){
			cell.setCellValue(new Date());  
			HSSFCellStyle cellStyle = wb.createCellStyle();
			//指定日期显示格式
			cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));  
			cell.setCellValue((Date)value);  
			//设定单元格日期显示格式
			cell.setCellStyle(cellStyle);  
		}
		

	
		
		
	}
	
	public static void main(String[] args) {
		 List<Role> lfs=new ArrayList<Role>();
		 for(int i=0;i<5;i++){
			 
				Role f=new Role();
				f.setRole_name("role_name "+i);
				f.setRole_id(i+881);
				lfs.add(f);
		 }
		try {
			xlsDto2Excel(lfs,Role.class,null,null,null);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
	
   
}
